# -*- coding: utf-8 -*-
# SQLAlchemy 连接 MySQL 数据库入门示例

from sqlalchemy import create_engine, Column, Integer, String, DateTime, Date, Text, BIGINT, DECIMAL, JSON, TIMESTAMP, VARCHAR, ForeignKey, UniqueConstraint, Index
from sqlalchemy.dialects.mysql import TINYINT
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.sql import func
from datetime import datetime

# 1. 配置数据库连接
# 数据库连接字符串格式：mysql+pymysql://username:password@host:port/database
DATABASE_URL = "mysql+pymysql://root:20021010x@43.139.187.139:3307/blog"

# 2. 创建数据库引擎
engine = create_engine(
    DATABASE_URL,
    echo=True,  # 打印SQL语句，便于调试
    pool_pre_ping=True,  # 连接池预检查
    pool_recycle=3600  # 连接回收时间
)

# 3. 创建基类
Base = declarative_base()

# 4. 定义数据模型
class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(VARCHAR(50), unique=True, nullable=False)
    email = Column(VARCHAR(100), unique=True, nullable=False)
    password_hash = Column(VARCHAR(255), nullable=False)
    nickname = Column(VARCHAR(100), nullable=True)
    avatar = Column(VARCHAR(255), nullable=True)
    bio = Column(Text, nullable=True)
    phone = Column(VARCHAR(20), nullable=True)
    status = Column(TINYINT, nullable=False, default=1)
    created_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp())
    updated_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp(), onupdate=func.current_timestamp())

    __table_args__ = (
        Index('idx_status', 'status'),
    )


class Category(Base):
    __tablename__ = 'categories'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(VARCHAR(100), nullable=False)
    description = Column(Text, nullable=True)
    parent_id = Column(Integer, ForeignKey('categories.id'), nullable=True)
    sort_order = Column(Integer, nullable=False, default=0)
    status = Column(TINYINT, nullable=False, default=1)
    created_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp())
    updated_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp(), onupdate=func.current_timestamp())

    __table_args__ = (
        Index('idx_parent_id', 'parent_id'),
        Index('idx_status', 'status'),
        Index('idx_sort_order', 'sort_order'),
    )


class Location(Base):
    __tablename__ = 'locations'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(VARCHAR(255), nullable=False)
    address = Column(VARCHAR(500), nullable=True)
    latitude = Column(DECIMAL(10, 8), nullable=True)
    longitude = Column(DECIMAL(11, 8), nullable=True)
    country = Column(VARCHAR(100), nullable=True)
    province = Column(VARCHAR(100), nullable=True)
    city = Column(VARCHAR(100), nullable=True)
    district = Column(VARCHAR(100), nullable=True)
    created_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp())
    updated_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp(), onupdate=func.current_timestamp())

    __table_args__ = (
        Index('idx_lat_lng', 'latitude', 'longitude'),
        Index('idx_location', 'country', 'province', 'city'),
    )


class Article(Base):
    __tablename__ = 'articles'

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(VARCHAR(255), nullable=False)
    content = Column(Text, nullable=False)
    summary = Column(Text, nullable=True)
    author_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    category_id = Column(Integer, ForeignKey('categories.id'), nullable=True)
    cover_image = Column(VARCHAR(255), nullable=True)
    tags = Column(VARCHAR(500), nullable=True)
    view_count = Column(Integer, nullable=False, default=0)
    like_count = Column(Integer, nullable=False, default=0)
    comment_count = Column(Integer, nullable=False, default=0)
    status = Column(TINYINT, nullable=False, default=1)
    is_top = Column(TINYINT, nullable=False, default=0)
    location_id = Column(Integer, ForeignKey('locations.id'), nullable=True)
    published_at = Column(TIMESTAMP, nullable=True)
    created_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp())
    updated_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp(), onupdate=func.current_timestamp())

    __table_args__ = (
        Index('idx_author_id', 'author_id'),
        Index('idx_category_id', 'category_id'),
        Index('idx_status', 'status'),
        Index('idx_published_at', 'published_at'),
        Index('idx_top_published', 'is_top', 'published_at'),
        Index('idx_location_id', 'location_id'),
    )


class Comment(Base):
    __tablename__ = 'comments'

    id = Column(Integer, primary_key=True, autoincrement=True)
    article_id = Column(Integer, ForeignKey('articles.id'), nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    parent_id = Column(Integer, ForeignKey('comments.id'), nullable=True)
    content = Column(Text, nullable=False)
    like_count = Column(Integer, nullable=False, default=0)
    ip_address = Column(VARCHAR(45), nullable=True)
    user_agent = Column(VARCHAR(500), nullable=True)
    status = Column(TINYINT, nullable=False, default=1)
    created_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp())
    updated_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp(), onupdate=func.current_timestamp())

    __table_args__ = (
        Index('idx_article_id', 'article_id'),
        Index('idx_user_id', 'user_id'),
        Index('idx_parent_id', 'parent_id'),
        Index('idx_status', 'status'),
        Index('idx_created_at', 'created_at'),
    )


class Image(Base):
    __tablename__ = 'images'

    id = Column(Integer, primary_key=True, autoincrement=True)
    filename = Column(VARCHAR(255), nullable=False)
    file_path = Column(VARCHAR(500), nullable=False)
    file_size = Column(BIGINT, nullable=False)
    file_type = Column(VARCHAR(50), nullable=False)
    width = Column(Integer, nullable=True)
    height = Column(Integer, nullable=True)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    usage_type = Column(TINYINT, nullable=False, default=1)
    status = Column(TINYINT, nullable=False, default=1)
    created_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp())

    __table_args__ = (
        Index('idx_user_id', 'user_id'),
        Index('idx_usage_type', 'usage_type'),
        Index('idx_status', 'status'),
        Index('idx_created_at', 'created_at'),
    )


class UserLike(Base):
    __tablename__ = 'user_likes'

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    target_type = Column(TINYINT, nullable=False)
    target_id = Column(Integer, nullable=False)
    created_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp())

    __table_args__ = (
        UniqueConstraint('user_id', 'target_type', 'target_id', name='uq_user_target'),
        Index('idx_target', 'target_type', 'target_id'),
    )


class Role(Base):
    __tablename__ = 'roles'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(VARCHAR(50), unique=True, nullable=False)
    display_name = Column(VARCHAR(100), nullable=False)
    description = Column(VARCHAR(255), nullable=True)
    permissions = Column(JSON, nullable=True)
    level = Column(TINYINT, nullable=False, default=1)
    status = Column(TINYINT, nullable=False, default=1)
    created_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp())
    updated_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp(), onupdate=func.current_timestamp())

    __table_args__ = (
        Index('idx_level', 'level'),
        Index('idx_status', 'status'),
    )


class UserRole(Base):
    __tablename__ = 'user_roles'

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    role_id = Column(Integer, ForeignKey('roles.id'), nullable=False)
    assigned_by = Column(Integer, ForeignKey('users.id'), nullable=True)
    assigned_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp())
    expires_at = Column(TIMESTAMP, nullable=True)
    status = Column(TINYINT, nullable=False, default=1)
    created_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp())

    __table_args__ = (
        UniqueConstraint('user_id', 'role_id', name='uq_user_role'),
        Index('idx_user_id', 'user_id'),
        Index('idx_role_id', 'role_id'),
        Index('idx_assigned_by', 'assigned_by'),
        Index('idx_status_expires', 'status', 'expires_at'),
    )


class SystemConfig(Base):
    __tablename__ = 'system_config'

    id = Column(Integer, primary_key=True, autoincrement=True)
    config_key = Column(VARCHAR(100), unique=True, nullable=False)
    config_value = Column(Text, nullable=True)
    description = Column(VARCHAR(255), nullable=True)
    type = Column(VARCHAR(20), nullable=False, default='string')
    created_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp())
    updated_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp(), onupdate=func.current_timestamp())


class StudySubject(Base):
    """学习科目表"""
    __tablename__ = 'study_subjects'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(VARCHAR(100), nullable=False, comment='科目名称')
    description = Column(Text, nullable=True, comment='科目描述')
    icon = Column(VARCHAR(255), nullable=True, comment='科目图标路径')
    color = Column(VARCHAR(20), nullable=True, default='#1890ff', comment='科目颜色标识')
    target_days = Column(Integer, nullable=False, default=0, comment='目标打卡天数，0表示无限制')
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False, comment='创建者用户ID')
    is_public = Column(TINYINT, nullable=False, default=0, comment='是否公开：0-私有，1-公开')
    sort_order = Column(Integer, nullable=False, default=0, comment='排序顺序')
    status = Column(TINYINT, nullable=False, default=1, comment='状态：0-禁用，1-启用')
    created_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp())
    updated_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp(), onupdate=func.current_timestamp())

    __table_args__ = (
        Index('idx_user_id', 'user_id'),
        Index('idx_status', 'status'),
        Index('idx_sort_order', 'sort_order'),
    )


class StudyCheckIn(Base):
    """学习打卡记录表"""
    __tablename__ = 'study_check_ins'

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False, comment='打卡用户ID')
    subject_id = Column(Integer, ForeignKey('study_subjects.id'), nullable=False, comment='打卡科目ID')
    check_in_date = Column(Date, nullable=False, comment='打卡日期')  # 使用Date类型，只存储日期部分
    duration_minutes = Column(Integer, nullable=True, comment='学习时长（分钟）')
    content = Column(Text, nullable=True, comment='打卡笔记/学习内容')
    mood = Column(TINYINT, nullable=True, comment='学习心情：1-很差，2-一般，3-不错，4-很好，5-超棒')
    location_id = Column(Integer, ForeignKey('locations.id'), nullable=True, comment='打卡地点ID')
    images = Column(VARCHAR(1000), nullable=True, comment='打卡图片路径，多张图片用逗号分隔')
    status = Column(TINYINT, nullable=False, default=1, comment='状态：0-已删除，1-正常')
    created_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp())
    updated_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp(), onupdate=func.current_timestamp())

    __table_args__ = (
        UniqueConstraint('user_id', 'subject_id', 'check_in_date', name='uq_user_subject_date'),
        Index('idx_user_id', 'user_id'),
        Index('idx_subject_id', 'subject_id'),
        Index('idx_check_in_date', 'check_in_date'),
        Index('idx_status', 'status'),
    )


class StudyStatistics(Base):
    """学习统计表"""
    __tablename__ = 'study_statistics'

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False, comment='用户ID')
    subject_id = Column(Integer, ForeignKey('study_subjects.id'), nullable=True, comment='科目ID，NULL表示全部科目汇总')
    stat_date = Column(DateTime, nullable=False, comment='统计日期')
    check_in_count = Column(Integer, nullable=False, default=0, comment='打卡次数')
    total_duration = Column(Integer, nullable=False, default=0, comment='总学习时长（分钟）')
    continuous_days = Column(Integer, nullable=False, default=0, comment='连续打卡天数')
    max_continuous_days = Column(Integer, nullable=False, default=0, comment='最大连续打卡天数')
    created_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp())
    updated_at = Column(TIMESTAMP, nullable=False, server_default=func.current_timestamp(), onupdate=func.current_timestamp())

    __table_args__ = (
        UniqueConstraint('user_id', 'subject_id', 'stat_date', name='uq_user_subject_stat_date'),
        Index('idx_user_id', 'user_id'),
        Index('idx_subject_id', 'subject_id'),
        Index('idx_stat_date', 'stat_date'),
    )


# 5. 创建会话工厂
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)


# 6. 创建所有表
def create_tables():
    """创建所有数据库表"""
    try:
        Base.metadata.create_all(bind=engine)
        print("✅ 所有数据库表创建成功！")
        print("\n📋 创建的表列表:")
        print("- users (用户表)")
        print("- categories (文章分类表)")
        print("- locations (定位表)")
        print("- articles (文章表)")
        print("- comments (评论表)")
        print("- images (图片上传表)")
        print("- user_likes (用户点赞表)")
        print("- roles (角色表)")
        print("- user_roles (用户角色关联表)")
        print("- system_config (系统配置表)")
        print("- study_subjects (学习科目表)")
        print("- study_check_ins (学习打卡记录表)")
        print("- study_statistics (学习统计表)")
        return True
    except Exception as e:
        print(f"❌ 创建数据库表失败: {e}")
        return False


# 7. 插入默认角色数据
def insert_default_roles():
    """插入默认角色数据"""
    session = SessionLocal()
    try:
        # 检查是否已有角色数据
        existing_roles = session.query(Role).count()
        if existing_roles > 0:
            print("⚠️  角色数据已存在，跳过默认角色创建")
            return

        # 创建默认角色
        default_roles = [
            {
                'name': 'user',
                'display_name': '普通用户',
                'description': '基础权限用户，可以浏览内容、发表评论',
                'level': 1,
                'permissions': {
                    "article": {"read": True},
                    "comment": {"create": True, "read": True, "update": ["own"], "delete": ["own"]},
                    "user": {"read": ["public"], "update": ["own"]}
                }
            },
            {
                'name': 'editor',
                'display_name': '编辑者',
                'description': '内容创建权限，可以发布文章、管理自己的内容',
                'level': 2,
                'permissions': {
                    "article": {"create": True, "read": True, "update": ["own"], "delete": ["own"]},
                    "comment": {"create": True, "read": True, "update": ["own"], "delete": ["own"]},
                    "user": {"read": ["public"], "update": ["own"]}
                }
            },
            {
                'name': 'admin',
                'display_name': '管理员',
                'description': '内容管理权限，可以管理所有内容、用户管理',
                'level': 3,
                'permissions': {
                    "article": {"create": True, "read": True, "update": ["all"], "delete": ["all"]},
                    "comment": {"create": True, "read": True, "update": ["all"], "delete": ["all"]},
                    "user": {"read": ["all"], "update": ["all"], "manage": True}
                }
            },
            {
                'name': 'super_admin',
                'display_name': '超级管理员',
                'description': '系统最高权限，可以管理角色和系统配置',
                'level': 4,
                'permissions': {
                    "article": {"create": True, "read": True, "update": ["all"], "delete": ["all"]},
                    "comment": {"create": True, "read": True, "update": ["all"], "delete": ["all"]},
                    "user": {"read": ["all"], "update": ["all"], "manage": True},
                    "system": {"config": True, "role_manage": True}
                }
            }
        ]

        for role_data in default_roles:
            role = Role(**role_data)
            session.add(role)

        session.commit()
        print("✅ 默认角色数据插入成功！")
        print("\n📋 创建的默认角色:")
        for role in default_roles:
            print(f"- {role['name']}: {role['display_name']} (级别: {role['level']})")

    except Exception as e:
        session.rollback()
        print(f"❌ 插入默认角色数据失败: {e}")
    finally:
        session.close()


# 8. 主函数
def main():
    """主函数"""
    print("🚀 开始创建博客数据库表...")
    print(f"📡 数据库连接: {DATABASE_URL.replace('20021010x', '****')}")
    print("-" * 50)

    # 创建表
    if create_tables():
        # 插入默认数据
        insert_default_roles()
        print("-" * 50)
        print("🎉 数据库初始化完成！")
    else:
        print("-" * 50)
        print("💥 数据库初始化失败！")


if __name__ == "__main__":
    main()